package com.siqi.hr.common.exportexcel;

import com.siqi.hr.system.custom.exception.ExcelException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.Calendar;

public class ExportExcel2007BySXSSF implements ExportExcel {
	private static String DATE_FORMAT = "yyyy-mm-dd HH:mm:ss";
	private static String NUMBER_FORMAT = " #,##0.00 ";
	private String xlsxFileName;
	private Workbook workbook;
	private Sheet sheet;
	private Row row;

	public ExportExcel2007BySXSSF(String fileName) {
		this.xlsxFileName = fileName;
		this.workbook = new SXSSFWorkbook(5000);
		this.sheet = this.workbook.createSheet();
	}

	public void export() throws ExcelException {
		try {
			FileOutputStream fOut = new FileOutputStream(this.xlsxFileName);
			this.workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (FileNotFoundException e) {
			throw new ExcelException("生成导出Excel文件出错!", e);
		} catch (IOException e) {
			throw new ExcelException("写入Excel文件出错!", e);
		}
	}

	public String getCellReference(int index) {
		Cell cell = this.row.createCell(index);
		return new CellReference(cell).formatAsString();
	}

	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}

	public void setCell(int index, String value) {
		Cell cell = this.row.createCell(index);
		cell.setCellType(1);
		cell.setCellValue(value);
		CellStyle cellStyle = configExcelStyle(this.workbook);
		cellStyle
				.setDataFormat(this.workbook.createDataFormat().getFormat("@"));
		cell.setCellStyle(cellStyle);
	}

	public void setCell(int index, Timestamp value) {
		Cell cell = this.row.createCell(index);

		cell.setCellValue(value.getTime());
		CellStyle cellStyle = configExcelStyle(this.workbook);
		cellStyle.setDataFormat(this.workbook.createDataFormat().getFormat(
				DATE_FORMAT));
		cell.setCellStyle(cellStyle);
	}

	public void setCell(int index, Calendar value) {
		Cell cell = this.row.createCell(index);
		cell.setCellValue(value.getTime());
		CellStyle cellStyle = configExcelStyle(this.workbook);
		cellStyle.setDataFormat(this.workbook.createDataFormat().getFormat(DATE_FORMAT));
		cell.setCellStyle(cellStyle);
	}

	public void setCell(int index, int value) {
		Cell cell = this.row.createCell(index);
		cell.setCellType(0);
		cell.setCellValue(value);
		CellStyle cellStyle = configExcelStyle(this.workbook);
		cell.setCellStyle(cellStyle);
	}

	public void setCell(int index, double value) {
		Cell cell = this.row.createCell(index);
		cell.setCellType(0);
		cell.setCellValue(value);
		CellStyle cellStyle = configExcelStyle(this.workbook);
		DataFormat format = this.workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
		cell.setCellStyle(cellStyle);
	}

	public void setCell(int index, boolean value) {
	}

	public CellStyle configExcelStyle(Workbook wb) {
		Font font = wb.createFont();
		font.setFontName("Verdana");
		font.setBoldweight((short) 100);
		font.setFontHeight((short) 300);

		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setAlignment((short) 2);
		style.setVerticalAlignment((short) 1);

		style.setFillPattern((short) 1);

		style.setBorderBottom((short) 7);

		style.setBorderTop((short) 5);

		return style;
	}
}
